﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Text;

public partial class SQLTOFile : System.Web.UI.Page
{


    protected void Page_Load(object sender, EventArgs e)
    {
        uxConnectionString.Text = Session["CONNSTR"].ToString();
    }
    protected void uxRunSQL_Click(object sender, EventArgs e)
    {
        SqlDataAdapter da = new SqlDataAdapter(uxSQLtxt.Text.Trim(), Session["CONNSTR"].ToString());
        DataSet ds = new DataSet();
        da.Fill(ds);
        uxgv.DataSource = ds;
        uxgv.DataBind();
    }
    protected void uxExport_Click(object sender, EventArgs e)
    {
        switch (uxFileType.SelectedValue)
        {
            case "01":
                ExportToTextFile();
                break;
            case "02":
                ExportToExcelFile();
                break;
            case "03":
                ExportToXMLFile();
                break;
            case "04":
                ExportToCSVFile();
                break;
            default:
                break;
        }
        //ExportToTextFile();
    }

    public void ExportToTextFile()
    {
        using (StreamWriter sr = new StreamWriter(Server.MapPath("") + @"\temp\Data.csv",
                                    false, 
                                    Encoding.Default))
        {
            int rcnt = 0;
            foreach (GridViewRow gvr in uxgv.Rows)
            {

                //if (rcnt == 0)
                //{
                //    rcnt++;
                //    continue;
                //}
                string str = "";
                for (int i = 0; i < gvr.Cells.Count; i++)
                {
                    str += gvr.Cells[i].Text.Replace("&nbsp;", "");
                }
                sr.WriteLine(str);
                rcnt++;
            }

            if (rcnt > 0)
            {
                uxFilePath.Visible = true;
                uxFilePath.NavigateUrl = "~/temp/Data.txt";
            }
        }
    }

    public void ExportToExcelFile()
    {
        Response.Clear();
        Response.AddHeader("content-disposition", "attachment; filename=FileName.xls");
        Response.Charset = "";
        Response.ContentType = "application/vnd.xls";
        System.IO.StringWriter stringWrite = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htmlWrite =
        new HtmlTextWriter(stringWrite);
        uxgv.RenderControl(htmlWrite);
        Response.Write(stringWrite.ToString());
        Response.End();
    }

    public void ExportToXMLFile()
    {
        SqlDataAdapter da = new SqlDataAdapter(uxSQLtxt.Text.Trim(), Session["CONNSTR"].ToString());
        DataSet ds = new DataSet();
        da.Fill(ds);
        ds.WriteXml(Server.MapPath("") + @"\temp\Data.xml");
        uxFilePath.Visible = true;
        uxFilePath.NavigateUrl = "~/temp/Data.xml";
    }

    public void ExportToCSVFile()
    {
        using (StreamWriter sr = new StreamWriter(Server.MapPath("") + @"\temp\Data.csv",
                                    false,
                                    Encoding.Default))
        {
            SqlDataAdapter da = new SqlDataAdapter(uxSQLtxt.Text.Trim(), Session["CONNSTR"].ToString());
            DataSet ds = new DataSet();
            da.Fill(ds);
            if (ds.Tables[0].Rows.Count < 1)
                return;
            string str = "";
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                str = "";
                for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                {
                    str += ds.Tables[0].Rows[i][j].ToString() + ",";
                }

                sr.WriteLine(str.TrimEnd(','));
            }

            uxFilePath.Visible = true;
            uxFilePath.NavigateUrl = "~/temp/Data.csv";
        }
    }

    public override void VerifyRenderingInServerForm(Control control)
    {
        // Confirms that an HtmlForm control is rendered for the specified ASP.NET server control at run time.
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        if (TableAndColumn1.SQLString.Length > 0)
        {
            uxSQLtxt.Text = TableAndColumn1.SQLString.Replace("#TOP#", DropDownList1.SelectedValue);
        }
    }
}
